Author: Christos Psarropoulos

Purpose

this report presents the business case for the formulation of a Customer Segmentation strategy by utilizing the information available in a CRM system.

CRM Extract

The data we are analysing refer to a multinational IT company which sells 62 unique products across 4 Product Categories in 17 countries. Key Account managers list opportunities in the company’s CRM system each opportunity takes an unique opportunity ID.

lets have a look in our crm data

#importing the data in a dataframe from csv file
crm<-read.csv("crm.csv", header = TRUE,sep = ";")
describe(crm)
## crm 
## 
##  18  Variables      5766  Observations
## --------------------------------------------------------------------------------
## Billing.Country 
##        n  missing distinct 
##     5766        0       17 
## 
## lowest : Albania                Bosnia and Herzegovina Bulgaria               Croatia                Cyprus                
## highest: Serbia                 Slovakia               Slovenia               Turkey                 Ukraine               
## 
## Albania (235, 0.041), Bosnia and Herzegovina (257, 0.045), Bulgaria (1604,
## 0.278), Croatia (578, 0.100), Cyprus (65, 0.011), Czech Republic (109, 0.019),
## FYROM (158, 0.027), Greece (541, 0.094), Kosovo (149, 0.026), Moldova, Republic
## of (9, 0.002), Montenegro (48, 0.008), Romania (517, 0.090), Serbia (222,
## 0.039), Slovakia (215, 0.037), Slovenia (794, 0.138), Turkey (43, 0.007),
## Ukraine (222, 0.039)
## --------------------------------------------------------------------------------
## Opportunity.ID 
##        n  missing distinct 
##     5766        0     2042 
## 
## lowest : OPP-000999 OPP-001000 OPP-001078 OPP-001192 OPP-001193
## highest: OPP-011751 OPP-011753 OPP-011757 OPP-011804 OPP-012009
## --------------------------------------------------------------------------------
## Account.Name 
##        n  missing distinct     Info     Mean      Gmd      .05      .10 
##     5766        0     2042        1     8623     1929     2850     7383 
##      .25      .50      .75      .90      .95 
##     8183     8780     9819    10632    10916 
## 
## lowest :   999  1000  1078  1192  1193, highest: 11751 11753 11757 11804 12009
## --------------------------------------------------------------------------------
## Parent.Account 
##        n  missing distinct 
##     5766        0       15 
## 
## lowest :           Parent_1  Parent_10 Parent_11 Parent_12
## highest: Parent_5  Parent_6  Parent_7  Parent_8  Parent_9 
## 
## (4157, 0.721), Parent_1 (49, 0.008), Parent_10 (61, 0.011), Parent_11 (6,
## 0.001), Parent_12 (62, 0.011), Parent_13 (8, 0.001), Parent_14 (2, 0.000),
## Parent_2 (457, 0.079), Parent_3 (172, 0.030), Parent_4 (75, 0.013), Parent_5
## (293, 0.051), Parent_6 (175, 0.030), Parent_7 (65, 0.011), Parent_8 (130,
## 0.023), Parent_9 (54, 0.009)
## --------------------------------------------------------------------------------
## Type 
##        n  missing distinct 
##     5766        0        2 
##                                               
## Value      Existing Business      New Business
## Frequency               4867               899
## Proportion             0.844             0.156
## --------------------------------------------------------------------------------
## Invoice.Date 
##        n  missing distinct 
##     5766        0      283 
## 
## lowest : 1/13/2017 1/14/2017 1/16/2017 1/17/2017 1/19/2017
## highest: 9/26/2017 9/27/2017 9/29/2017 9/30/2016 9/30/2017
## --------------------------------------------------------------------------------
## Schedule.Date 
##        n  missing distinct 
##     5766        0      321 
## 
## lowest : 1/1/17    1/10/17   1/11/17   1/12/17   1/13/2017
## highest: 9/3/17    9/30/2017 9/5/17    9/6/17    9/8/17   
## --------------------------------------------------------------------------------
## Amount..converted..Currency 
##        n  missing distinct    value 
##     5766        0        1      EUR 
##                
## Value       EUR
## Frequency  5766
## Proportion    1
## --------------------------------------------------------------------------------
## Amount..converted. 
##        n  missing distinct     Info     Mean      Gmd      .05      .10 
##     5766        0     1774        1   113208   178297    370.3    763.1 
##      .25      .50      .75      .90      .95 
##   3717.8  19584.0  82612.9 335143.4 653012.1 
## 
## lowest :       0.27      15.34      15.73      26.60      29.00
## highest: 1830000.00 2052300.00 2075910.32 2090000.00 2104000.00
## --------------------------------------------------------------------------------
## Quantity 
##        n  missing distinct     Info     Mean      Gmd      .05      .10 
##     5766        0      349    0.974     2474     4723        1        1 
##      .25      .50      .75      .90      .95 
##        1       12      242     1858     7047 
## 
## lowest :      1.0      2.0      3.0      3.5      4.0
## highest:  52800.0  62500.0  74259.0 149829.0 552715.0
##                                                                          
## Value           0   5000  10000  15000  20000  30000  35000  40000  45000
## Frequency    5247    237    119     40     53     13      1      5     24
## Proportion  0.910  0.041  0.021  0.007  0.009  0.002  0.000  0.001  0.004
##                                              
## Value       55000  60000  75000 150000 555000
## Frequency       1      1     12      1     12
## Proportion  0.000  0.000  0.002  0.000  0.002
## 
## For the frequency table, variable is rounded to the nearest 5000
## --------------------------------------------------------------------------------
## Schedule.Amount..converted..Currency 
##        n  missing distinct    value 
##     5766        0        1      EUR 
##                
## Value       EUR
## Frequency  5766
## Proportion    1
## --------------------------------------------------------------------------------
## Schedule.Amount..converted. 
##         n   missing  distinct      Info      Mean       Gmd       .05       .10 
##      5766         0      3109         1     30864     54336     51.13    145.02 
##       .25       .50       .75       .90       .95 
##    600.00   2375.00  11063.05  46558.75 125000.00 
## 
## lowest :  -11394.63   -3436.90   -1986.04   -1315.20    -986.40
## highest: 1038826.44 1122000.00 1162431.00 1304000.00 1572100.00
## --------------------------------------------------------------------------------
## Gross.Margin.. 
##        n  missing distinct     Info     Mean      Gmd      .05      .10 
##     5766        0      542    0.996    45.05     33.5    3.907    6.895 
##      .25      .50      .75      .90      .95 
##   20.000   43.730   60.000  100.000  100.000 
## 
## lowest :   0.01   0.10   0.97   1.00   1.38, highest: 119.00 179.00 308.00 630.00 900.00
##                                                                             
## Value          0    10    20    30    40    50    60    70    80    90   100
## Frequency    475   569   782   493   646   920   704   203   249    55   665
## Proportion 0.082 0.099 0.136 0.086 0.112 0.160 0.122 0.035 0.043 0.010 0.115
##                                         
## Value        120   180   310   630   900
## Frequency      1     1     1     1     1
## Proportion 0.000 0.000 0.000 0.000 0.000
## 
## For the frequency table, variable is rounded to the nearest 10
## --------------------------------------------------------------------------------
## Stage 
##        n  missing distinct 
##     5766        0        2 
##                                 
## Value      Closed Won   Invoiced
## Frequency        1878       3888
## Proportion      0.326      0.674
## --------------------------------------------------------------------------------
## Probability.... 
##        n  missing distinct     Info     Mean      Gmd 
##     5766        0        2    0.659    96.74    4.393 
##                       
## Value         90   100
## Frequency   1878  3888
## Proportion 0.326 0.674
## --------------------------------------------------------------------------------
## Forecast.Category 
##        n  missing distinct 
##     5766        0        2 
##                         
## Value      Closed Commit
## Frequency    3888   1878
## Proportion  0.674  0.326
## --------------------------------------------------------------------------------
## Product.Name 
##        n  missing distinct 
##     5766        0       62 
## 
## lowest : Product_1  Product_10 Product_11 Product_12 Product_13
## highest: Product_61 Product_62 Product_7  Product_8  Product_9 
## --------------------------------------------------------------------------------
## Product_.Category 
##        n  missing distinct 
##     5766        0        4 
##                                                           
## Value         Hardware Outsourcing    Services    Software
## Frequency         1662         241        2961         902
## Proportion       0.288       0.042       0.514       0.156
## --------------------------------------------------------------------------------

as we can see our data constist of 18 variables and 5766 observations and there are no missing observations for the our variables.

Above we can find the relative frequency and the proportion were applicable for each variable and drow the following conclusions:

  • 3888 opportunities are “Invoiced” and 1878 are “Closed-Won”" this means that we can use the whole dataset in our analysis.
  • 3 Countries are generating 52% of the total sales (Bulgaria: 28%, Slovenia: 14%, Croatia : 10%).
  • 85% of the total Sales are coming from Existing Customers vs 15% that comes from New.
  • 51% of Sales is coming from Services, 29% from Hardware, 16% from Software and 4% from Outsourcing
  • There are 2042 distinct Accounts (i.e. Customers). Also there are 15 Parent Accounts (country )
  • Mean Revenue from a sale is 30.864 €
  • Mean Gross Marging Percentage of a Sale is 45.05%

Data Sample

Since our data concists of 5766 observations we take a random sample of 28 entries to see a preview of the data we are working on

#taking a random sample of our data 0,5%
(formattable(sample_frac(crm,0.005)))
Billing.Country Opportunity.ID Account.Name Parent.Account Type Invoice.Date Schedule.Date Amount..converted..Currency Amount..converted. Quantity Schedule.Amount..converted..Currency Schedule.Amount..converted. Gross.Margin.. Stage Probability…. Forecast.Category Product.Name Product_.Category
Bulgaria OPP-008773 8773 New Business 43009 10/6/17 EUR 74518.01 828 EUR 6209.83 50.00 Invoiced 100 Closed Product_5 Services
Ukraine OPP-002853 2853 Existing Business 42705 1/3/17 EUR 122113.07 4933 EUR 3545.02 25.00 Closed Won 90 Commit Product_5 Services
Bulgaria OPP-008773 8773 New Business 43009 10/1/17 EUR 74518.01 828 EUR 6209.83 50.00 Invoiced 100 Closed Product_5 Services
Romania OPP-003651 3651 New Business 12/30/2016 7/25/2017 EUR 60216.00 12 EUR 5018.00 45.00 Invoiced 100 Closed Product_33 Services
FYROM OPP-008517 8517 Existing Business 42736 1/8/17 EUR 15166.20 9420 EUR 1263.85 100.00 Closed Won 90 Commit Product_6 Services
Bulgaria OPP-009955 9955 Parent_3 Existing Business 5/30/2017 5/30/2017 EUR 100.00 2 EUR 100.00 25.00 Invoiced 100 Closed Product_26 Hardware
Romania OPP-008069 8069 Parent_8 Existing Business 43076 9/30/2017 EUR 15132.00 16 EUR 3783.00 5.56 Invoiced 100 Closed Product_27 Software
Slovenia OPP-009347 9347 Existing Business 8/24/2017 9/30/2017 EUR 15300.00 1 EUR 900.00 66.67 Invoiced 100 Closed Product_31 Services
Bulgaria OPP-009701 9701 Existing Business 42829 4/4/17 EUR 3913.33 1 EUR 3913.33 20.00 Invoiced 100 Closed Product_14 Software
Slovenia OPP-009155 9155 New Business 42737 1/2/17 EUR 1080.00 6 EUR 1080.00 80.00 Closed Won 90 Commit Product_39 Services
Ukraine OPP-002862 2862 Existing Business 42705 1/3/17 EUR 6509.51 397 EUR 224.21 30.00 Closed Won 90 Commit Product_5 Services
Slovenia OPP-006988 6988 Existing Business 42924 7/8/17 EUR 19584.00 24 EUR 816.00 89.00 Closed Won 90 Commit Product_39 Services
Czech Republic OPP-008922 8922 Existing Business 4/30/2017 4/30/2017 EUR 2347.97 1 EUR 2347.97 100.00 Invoiced 100 Closed Product_6 Services
Slovenia OPP-006189 6189 Existing Business 6/30/2017 11/30/2017 EUR 9949.85 6 EUR 2312.47 15.00 Closed Won 90 Commit Product_6 Services
Bosnia and Herzegovina OPP-008430 8430 Existing Business 1/31/2017 5/25/2017 EUR 22574.76 18529 EUR 3869.90 56.00 Closed Won 90 Commit Product_5 Services
Romania OPP-008021 8021 Existing Business 3/31/2017 3/31/2017 EUR 15200.00 1 EUR 3800.00 10.00 Closed Won 90 Commit Product_27 Software
Bulgaria OPP-008765 8765 Parent_5 Existing Business 42826 4/7/17 EUR 364360.87 6152 EUR 31021.60 50.00 Invoiced 100 Closed Product_5 Services
Slovenia OPP-008214 8214 Parent_5 Existing Business 1/28/2017 6/28/2017 EUR 48429.00 172 EUR 1755.00 64.94 Invoiced 100 Closed Product_5 Services
Croatia OPP-002753 2753 New Business 12/31/2020 5/31/2017 EUR 100535.00 24 EUR 2861.00 10.00 Closed Won 90 Commit Product_1 Outsourcing
Bulgaria OPP-011669 11669 Existing Business 12/29/2017 12/29/2017 EUR 1777.48 1 EUR 1777.48 20.00 Invoiced 100 Closed Product_26 Hardware
Romania OPP-009114 9114 Parent_7 New Business 3/23/2017 3/23/2017 EUR 41507.50 170 EUR 2380.00 37.00 Invoiced 100 Closed Product_7 Software
Bosnia and Herzegovina OPP-009372 9372 Existing Business 2/28/2017 2/28/2017 EUR 488.80 1 EUR 488.80 100.00 Invoiced 100 Closed Product_5 Services
Slovenia OPP-010013 10013 Existing Business 10/31/2017 10/31/2017 EUR 63033.00 1 EUR 1836.00 78.00 Invoiced 100 Closed Product_31 Services
Albania OPP-009784 9784 Parent_1 Existing Business 6/30/2017 1/4/17 EUR 18964.50 991 EUR 888.30 100.00 Invoiced 100 Closed Product_1 Outsourcing
Slovenia OPP-008213 8213 Existing Business 1/28/2017 1/28/2017 EUR 45528.00 480 EUR 0.00 71.78 Invoiced 100 Closed Product_5 Services
Serbia OPP-008691 8691 Parent_6 New Business 1/31/2017 2/28/2017 EUR 100620.00 129 EUR 8385.00 40.00 Invoiced 100 Closed Product_5 Services
Bulgaria OPP-004870 4870 Existing Business 2/22/2016 1/1/17 EUR 587412.96 5896 EUR 0.00 50.00 Invoiced 100 Closed Product_5 Services
Croatia OPP-001000 1000 New Business 12/31/2020 4/30/2017 EUR 32388.30 231 EUR 1270.74 100.00 Closed Won 90 Commit Product_6 Services
Turkey OPP-009181 9181 New Business 42377 1/1/17 EUR 78277.89 1 EUR 19569.47 30.80 Invoiced 100 Closed Product_17 Software

Variables Description

  • Billing.Country: The country in which the customer operates.
  • Opportunity.ID : Unique system ID for each opportunity listed in the CRM.
  • Account.Name : Customer Name.
  • Parent.Account: Some customers operate
  • Type : if the customer is a returning customer (“Existing”) or a new customer (“New Business”)
  • Invoice.Date: the date that we invoiced the customer.
  • Schedule.Date: In some cases we invoice based on a schedule especially if the opportunity has i) many products that have different deliveries dates attached to it. ii) we have an outsourcing or a service contract in place where we invoice the customer monthly so in that case the Invoice.Date could by 30/12/2017 and we will have 12 entries with different Schedule.Date.
  • Amount..converted..Currency: Denotes the currency of the Amount..converted..
  • Amount..converted. : Most CRM systems convert the local country currency to a predifined one for management reporting purposes in our case the selected currency is EUR.
  • Quantity : Product quantity.
  • Schedule.Amount..converted..Currency: Denotes the currency of the Schedule.Amount..converted.
  • Schedule.Amount..converted.: When we invoice based on a schedule the total Amount..converted. of the opportunity is broken down based on the schedule.
  • Gross.Margin..: the Gross Margin of each opportunity.
  • Stage : the stage of the opportunity i.e invoiced, Closed Won
  • Probability.... : the probability of completion for the opportunity.
  • Forecast.Category: Characterizes if an opportunity is Closed or Committed mainly used for pipeline monitoring purposes.
  • Product.Name: The name of the product.
  • Product.Category : Category of the product i.e Hardware, Services , Outsourcing, Software

What is RFM analysis

Describes current customers historical purchase behaviour using 3 features
  • Recency= When we last invoiced the customer? (counted in days)

  • Frequency= How often do we invoice the customer?

  • Monetary value= What is the monetary value the customer? i.e Revenue/Frequency

Once we have R,F,M values from the purchase history,we assign a score from one (1) to five (5) to Recency, Frequency and Monetary values individually for each customer.

Five (5) is the best/highest value, and one (1) is the lowest/worst value.

A final RFM score is calculated simply by combining individual R,F,M score numbers. then we can segment our customers based on the rules in the following table

Recency Score Combined FM Score Segment
5 5 Champions
4-5 4-5 Loyal
4-5 0-3 Promising
2-3 2-3 Needs Attention
2-4 1-4 Slipping
1-3 0-5 At Risk

RFM Analysis

We will use for our analyis on the Schedule.Amount..converted. and the Schedule.Date fields for the following reason since there are opportunities which are depicted in the system with multiple lines for the same Opportunity.ID those two fields correspond to the true sale revenue and the true invoice date. Also we will group our crm dataset by Billing.Country,Account.Name and Parent.Account cause we must define the whole purchasing history we have with each customer and each customer might have various opportunities listed to them.

we choose as a report date 31/12/2017 Now we need to calculate some new variables

  • Revenue: Invoiced Revenue corresponding to each customer.In order to calculate revenue we will sum up all the Schedule.Amount..converted. per Account.Name.

  • GrossProffit:Gross Proffit per customer.In order to calculate Gross Profit we will used the following formula Revenue x Gross.Margin...

  • Recency: When did we last invoiced the customer, counted in days. In order to calculate recency we will take the difference in days from our report date (i.e.31/12/2017) from the maximum Schedule.Date for each customer. This ensures that if a customer has more than one opportunities listed the most recent invoice date will be used for the calculation.

  • Frequency: How often do we invoice the customer. In order to calculate frequency will count the distict Schedule.Date for each customer.So if we have issued multiple invoices related with multiple opportunities for the same customer in the same date this will be counted as a one time invoicing.
#Setting a report date 
rdate2017=as.Date("2017-12-31")

# CRM data wrangling 1 :date transformations and filtering and saving the results in a new dataframe named seg2017
seg2017<-crm%>%mutate(`Schedule.Date`=mdy(`Schedule.Date`))%>%filter(Schedule.Date > "2016-01-31" & Schedule.Date <"2018-01-01")
#%>%mutate(`Invoice.Date`=mdy(`Invoice.Date`))

#CRM data wrangling 2:calculating Gross Profit in the seg2017 dataframe
seg2017<-seg2017%>%mutate(`Gross.Profit`=(Schedule.Amount..converted.*Gross.Margin..)/100)
# grouping seg2017 calculating Recency/Frequncy/Monetary/Profit and Gross Profit 2017 and saving the results in a new datarame named RFM_seg2017
RFM_seg2017<-seg2017%>%group_by(Billing.Country,Account.Name,Parent.Account)%>%
  summarise(Recency=as.numeric(rdate2017-max(Schedule.Date)),Frequency=n_distinct(Schedule.Date),Monetary= sum(as.numeric(Schedule.Amount..converted.),na.rm =TRUE)/n_distinct(Schedule.Date),Revenue=sum(as.numeric(Schedule.Amount..converted.),na.rm=TRUE),  `Gross.Profit`=sum(Gross.Profit,na.rm=TRUE))

# Order the RFM_seg2017 dataframe by Revenue
RFM_seg2017<-RFM_seg2017[order(-RFM_seg2017$Revenue),]

#present a few lines of RFM_seg2017
formattable(head(RFM_seg2017,15))
Billing.Country Account.Name Parent.Account Recency Frequency Monetary Revenue Gross.Profit
Croatia 8575 25 14 4097515.7 57365220 5092530.8
Croatia 8576 52 14 690870.1 9672182 974449.4
Romania 8036 Parent_2 0 3 1932092.4 5796277 1961179.5
Romania 8741 184 2 1956000.0 3912000 1312923.2
Greece 8132 0 7 498184.7 3487293 1679033.5
Greece 8118 87 5 532463.2 2662316 477234.1
Greece 10345 184 2 1052000.0 2104000 504333.2
Greece 8989 184 2 1026150.0 2052300 354498.3
Croatia 8751 180 4 511549.1 2046196 133875.9
Greece 10333 184 1 1830000.0 1830000 363823.2
Romania 7051 276 1 1734700.0 1734700 135306.6
Romania 8497 Parent_5 247 3 564213.3 1692640 223223.9
Greece 8129 353 9 177435.4 1596919 175661.1
Greece 10348 92 1 1551000.0 1551000 301248.9
Croatia 8864 Parent_8 92 5 293155.0 1465775 343090.4
#Calucalating R/F/M Quantiles 2017: taking the 3 columns (Recency, Frequency,Monetary) and scalling them 
rfm_quant_2017 <- RFM_seg2017[c(4:6)]%>%mutate(R=ntile(desc(Recency),5),F=ntile(Frequency,5),M=ntile(Monetary,5))%>%mutate(RFM= paste0(R,F,M))%>%mutate(RFM=as.numeric(RFM))%>%mutate(FM=ntile(F+M,5))
#creating an id column in order to join the 2 dataframes
 RFM_seg2017$id= 1:nrow(RFM_seg2017)
rfm_quant_2017$id=1:nrow(rfm_quant_2017)
RFM_seg2017<-full_join(RFM_seg2017,rfm_quant_2017)
## Joining, by = c("Recency", "Frequency", "Monetary", "id")
#dropping the id columnn 
RFM_seg2017 <- RFM_seg2017[-c(9)]
#characterise customers based on their RFM score 
RFM_seg2017<-RFM_seg2017%>%mutate(Segment=if_else(R%in% c(5)&FM%in%c(5),"Champions",if_else(R%in% c(4:5)&FM%in%c(4:5),"Loyal",if_else(R%in% c(4:5)&FM%in%c(0:3),"Promising",if_else(R%in% c(2:3)&FM%in%c(2:3),"Need Attention",if_else(R%in% c(2:4)&FM%in%c(1:4),"Slipping",if_else(R%in% c(1:3)&FM%in%c(0:5),"At Risk",false=NULL)))))))
formattable(head(RFM_seg2017,15))
Billing.Country Account.Name Parent.Account Recency Frequency Monetary Revenue Gross.Profit R F M RFM FM Segment
Croatia 8575 25 14 4097515.7 57365220 5092530.8 5 5 5 555 5 Champions
Croatia 8576 52 14 690870.1 9672182 974449.4 4 5 5 455 5 Loyal
Romania 8036 Parent_2 0 3 1932092.4 5796277 1961179.5 5 5 5 555 5 Champions
Romania 8741 184 2 1956000.0 3912000 1312923.2 2 4 5 245 5 At Risk
Greece 8132 0 7 498184.7 3487293 1679033.5 5 5 5 555 5 Champions
Greece 8118 87 5 532463.2 2662316 477234.1 4 5 5 455 5 Loyal
Greece 10345 184 2 1052000.0 2104000 504333.2 2 4 5 245 5 At Risk
Greece 8989 184 2 1026150.0 2052300 354498.3 2 4 5 245 5 At Risk
Croatia 8751 180 4 511549.1 2046196 133875.9 3 5 5 355 5 At Risk
Greece 10333 184 1 1830000.0 1830000 363823.2 2 1 5 215 3 Need Attention
Romania 7051 276 1 1734700.0 1734700 135306.6 1 1 5 115 3 At Risk
Romania 8497 Parent_5 247 3 564213.3 1692640 223223.9 2 5 5 255 5 At Risk
Greece 8129 353 9 177435.4 1596919 175661.1 1 5 5 155 5 At Risk
Greece 10348 92 1 1551000.0 1551000 301248.9 4 1 5 415 3 Promising
Croatia 8864 Parent_8 92 5 293155.0 1465775 343090.4 4 5 5 455 5 Loyal

Visualizations per Segment

#Ordering vector
myorder<-c('Champions','Loyal','Promising','Need Attention','Slipping','At Risk')

#Grouping RFM_seg2017 by Segment and creating a new df named revseg2017
revseg2017<-RFM_seg2017%>%group_by(Segment)%>%summarise(Revenue=sum(as.numeric(Revenue),na.rm=TRUE),No.Customers=sum(n_distinct(Account.Name)))%>%arrange(factor(Segment, levels =myorder))
revseg2017$Revenue<-currency(revseg2017$Revenue,symbol = "€",digits=0)

#showing revseg2017
formattable(revseg2017)
Segment Revenue No.Customers
Champions €88,535,821 170
Loyal €22,091,431 253
Promising €15,029,625 393
Need Attention €14,214,616 374
Slipping €2,225,945 340
At Risk €35,861,630 512
library(treemapify)
library(ggfittext)
library(viridis)
vectorc=c("red","orange","blue","green")

tree<-ggplot(revseg2017, aes(area =Revenue, fill =Revenue, label = paste(No.Customers,Segment,format(Revenue/1000000 ,digits=0,nsmall=2, big.mark=","),"M€")))+geom_treemap() +geom_treemap_text(fontface = "italic", colour = "white", place = "centre",grow = T,reflow=T,size=60)+theme(legend.position = "right")+labs(title =paste("Revenue by Customer Segment\n Total Revenue=",format(sum(revseg2017$Revenue),big.mark = ","),"€"),caption = "The area of each tile represents the proportion of Revenue")+scale_fill_continuous(limits=c(000000, 90000000), breaks=seq(0000000, 90000000,by=15000000),low="orangered1",high="darkblue",label = dollar_format(suffix = "€", prefix = ""))
tree

library(plotly)

pie <-revseg2017%>% plot_ly( labels = ~Segment, values = ~Revenue )%>%add_pie(hole = 0.6) %>%layout(title = 'Revenue by Segment',xaxis = list(showgrid = FALSE, zeroline = FALSE, showticklabels = FALSE),
         yaxis = list(showgrid = FALSE, zeroline = FALSE, showticklabels = FALSE)) 
#type = 'pie'after values in order to get a pie chart)>

pie%>%layout(showlegend = TRUE)%>% config(displayModeBar = F)
p <-revseg2017%>% plot_ly( y = ~Segment, x = ~Revenue, type = 'bar',orientation = 'h',height=450,width=750)%>%layout(margin =list(l = 110, r = 100, b = 50, t =50, pad = 4))
p%>%layout%>% config(displayModeBar = F)

Visualizations by Product

newdf<-merge(seg2017, RFM_seg2017[, c("Account.Name", "Segment")], by="Account.Name")
revprod<-newdf%>%group_by(Product.Name,Segment)%>% summarise(Revenue=sum(Schedule.Amount..converted.,na.rm=TRUE),No.Customers=sum(n_distinct(Account.Name)))
a<-revprod%>%group_by(Product.Name)%>%summarise(Revenue=sum(Revenue,na.rm=TRUE),No.Customers=sum(No.Customers,na.rm=TRUE))
a$Total="Total"
#unite(a, Product.Name, Total, sep=" ")


c<-bind_rows(revprod,a)
c[order(c$Product.Name),]
## # A tibble: 317 x 5
## # Groups:   Product.Name [62]
##    Product.Name Segment    Revenue No.Customers Total
##    <fct>        <chr>        <dbl>        <int> <chr>
##  1 Product_1    At Risk    138293.            4 <NA> 
##  2 Product_1    Champions 1452807.           10 <NA> 
##  3 Product_1    Loyal       37801.            2 <NA> 
##  4 Product_1    <NA>      1628901.           16 Total
##  5 Product_10   Loyal       27435.            6 <NA> 
##  6 Product_10   Promising   36649.            4 <NA> 
##  7 Product_10   Slipping    36571.            7 <NA> 
##  8 Product_10   <NA>       100655.           17 Total
##  9 Product_11   At Risk    168219.            5 <NA> 
## 10 Product_11   Champions   48758.            1 <NA> 
## # … with 307 more rows
d<-c%>%unite(Product.Name, Product.Name, Total, sep=" ")
d$Product.Name<- gsub("NA"," ",d$Product.Name)
d$Segment<- gsub("NAN","a",d$Segment)
d<-d[order(c$Product.Name,c$Revenue),]
d<-d%>%mutate(Segment=replace(Segment,is.na(Segment),""))%>%mutate(No.Customers=replace(No.Customers,is.na(No.Customers),""))
d$Revenue<-currency(d$Revenue,symbol = "€",digits=0)
formattable(d)
Product.Name Segment Revenue No.Customers
Product_1 Loyal €37,801 2
Product_1 At Risk €138,293 4
Product_1 Champions €1,452,807 10
Product_1 Total €1,628,901 16
Product_10 Loyal €27,435 6
Product_10 Slipping €36,571 7
Product_10 Promising €36,649 4
Product_10 Total €100,655 17
Product_11 Need Attention €6,000 1
Product_11 Promising €8,375 4
Product_11 Slipping €20,725 3
Product_11 Loyal €31,170 3
Product_11 Champions €48,758 1
Product_11 At Risk €168,219 5
Product_11 Total €283,248 17
Product_12 Promising €40,860 4
Product_12 Need Attention €56,590 4
Product_12 Slipping €90,014 9
Product_12 Loyal €173,990 2
Product_12 At Risk €226,703 10
Product_12 Champions €476,311 14
Product_12 Total €1,064,468 43
Product_13 At Risk €30,160 3
Product_13 Slipping €34,416 4
Product_13 Loyal €48,156 8
Product_13 Promising €127,023 6
Product_13 Champions €1,263,361 8
Product_13 Total €1,503,116 29
Product_14 Slipping €19,299 5
Product_14 Need Attention €81,956 2
Product_14 At Risk €204,539 6
Product_14 Promising €326,367 13
Product_14 Loyal €2,424,181 10
Product_14 Champions €11,361,472 2
Product_14 Total €14,417,815 38
Product_15 Loyal €5,798 1
Product_15 Champions €18,730 1
Product_15 Promising €39,900 1
Product_15 At Risk €73,000 2
Product_15 Need Attention €105,000 1
Product_15 Total €242,428 6
Product_16 Champions €2,600 1
Product_16 Need Attention €5,062 8
Product_16 Slipping €62,500 8
Product_16 Loyal €120,709 12
Product_16 At Risk €198,639 4
Product_16 Promising €263,161 23
Product_16 Total €652,671 56
Product_17 Need Attention €450 1
Product_17 Loyal €24,592 4
Product_17 Slipping €74,946 8
Product_17 Promising €181,310 7
Product_17 Champions €220,350 5
Product_17 At Risk €718,756 25
Product_17 Total €1,220,404 50
Product_18 Slipping €19,900 1
Product_18 Loyal €123,009 2
Product_18 Promising €149,342 6
Product_18 Total €292,251 9
Product_19 Promising €61,660 2
Product_19 Total €61,660 2
Product_2 Promising €4,950 1
Product_2 Loyal €10,088 1
Product_2 Champions €236,411 2
Product_2 At Risk €1,675,207 4
Product_2 Total €1,926,656 8
Product_20 Promising €34,384 2
Product_20 Total €34,384 2
Product_21 Loyal €2,100 1
Product_21 Promising €19,000 1
Product_21 Need Attention €37,900 1
Product_21 Total €59,000 3
Product_22 At Risk €74,438 1
Product_22 Total €74,438 1
Product_23 At Risk €25,202 1
Product_23 Promising €168,000 1
Product_23 Need Attention €262,000 1
Product_23 Total €455,202 3
Product_24 Slipping €6,068 2
Product_24 At Risk €15,250 1
Product_24 Champions €73,432 3
Product_24 Promising €174,070 1
Product_24 Total €268,820 7
Product_25 Slipping €285,075 29
Product_25 Promising €3,915,811 48
Product_25 At Risk €5,134,326 38
Product_25 Need Attention €6,316,483 55
Product_25 Loyal €7,641,185 24
Product_25 Champions €34,809,877 6
Product_25 Total €58,102,757 200
Product_26 Need Attention €21,235 51
Product_26 Slipping €66,718 21
Product_26 Promising €82,575 50
Product_26 At Risk €151,881 48
Product_26 Loyal €195,047 18
Product_26 Total €517,457 188
Product_27 Slipping €34,241 7
Product_27 Loyal €41,305 3
Product_27 At Risk €55,357 3
Product_27 Champions €194,621 8
Product_27 Promising €205,451 5
Product_27 Need Attention €230,447 5
Product_27 Total €761,422 31
Product_28 Slipping €4,008 1
Product_28 Promising €29,969 2
Product_28 Need Attention €132,000 1
Product_28 Total €165,977 4
Product_29 Promising €600 2
Product_29 Slipping €600 2
Product_29 Total €1,200 4
Product_3 Slipping €6,262 1
Product_3 Loyal €112,408 4
Product_3 Champions €218,856 3
Product_3 Total €337,525 8
Product_30 At Risk €33,260 1
Product_30 Total €33,260 1
Product_31 At Risk €1,556 3
Product_31 Slipping €2,500 2
Product_31 Champions €3,672 2
Product_31 Loyal €5,936 4
Product_31 Promising €9,726 5
Product_31 Need Attention €10,850 5
Product_31 Total €34,240 21
Product_32 Need Attention €1,660 3
Product_32 Loyal €6,011 3
Product_32 Promising €16,784 6
Product_32 At Risk €21,931 8
Product_32 Slipping €72,232 13
Product_32 Champions €179,880 1
Product_32 Total €298,497 34
Product_33 Need Attention €53,592 9
Product_33 Slipping €60,917 16
Product_33 Promising €84,572 11
Product_33 At Risk €377,024 22
Product_33 Loyal €1,033,291 9
Product_33 Champions €2,180,028 8
Product_33 Total €3,789,423 75
Product_34 Slipping €5,217 4
Product_34 At Risk €8,186 3
Product_34 Promising €21,858 3
Product_34 Need Attention €30,130 1
Product_34 Champions €79,322 2
Product_34 Loyal €108,701 2
Product_34 Total €253,414 15
Product_35 Loyal €6,208 2
Product_35 Promising €6,358 3
Product_35 Champions €12,480 2
Product_35 At Risk €82,255 3
Product_35 Total €107,301 10
Product_36 Promising €2,852 1
Product_36 Slipping €8,599 2
Product_36 At Risk €14,000 1
Product_36 Champions €61,160 1
Product_36 Total €86,611 5
Product_37 At Risk €7,066 2
Product_37 Champions €19,992 1
Product_37 Promising €21,995 9
Product_37 Slipping €49,654 7
Product_37 Need Attention €76,092 11
Product_37 Loyal €111,646 7
Product_37 Total €286,444 37
Product_38 At Risk €5,866 1
Product_38 Champions €12,224 1
Product_38 Loyal €17,598 3
Product_38 Slipping €17,598 3
Product_38 Promising €30,786 1
Product_38 Total €84,071 9
Product_39 Slipping €1,075 3
Product_39 Promising €1,300 3
Product_39 At Risk €26,540 6
Product_39 Loyal €26,810 3
Product_39 Champions €380,216 6
Product_39 Total €435,941 21
Product_4 Champions €3,487,293 1
Product_4 Total €3,487,293 1
Product_40 Loyal €5,232 1
Product_40 Promising €16,200 1
Product_40 Need Attention €47,200 1
Product_40 At Risk €65,500 2
Product_40 Champions €325,232 2
Product_40 Total €459,364 7
Product_41 Promising €15,338 1
Product_41 Loyal €25,450 3
Product_41 Slipping €54,271 6
Product_41 Total €95,059 10
Product_42 Loyal €3,000 1
Product_42 Promising €3,420 3
Product_42 Champions €6,000 2
Product_42 Need Attention €6,000 2
Product_42 At Risk €345,251 2
Product_42 Total €363,671 10
Product_43 Loyal €26,180 2
Product_43 Need Attention €104,658 2
Product_43 At Risk €108,000 1
Product_43 Champions €470,920 2
Product_43 Total €709,758 7
Product_44 Champions €24,615 1
Product_44 Slipping €29,994 11
Product_44 Promising €191,126 9
Product_44 Need Attention €291,034 7
Product_44 At Risk €413,186 10
Product_44 Loyal €2,114,791 12
Product_44 Total €3,064,747 50
Product_45 Champions €546 1
Product_45 At Risk €20,218 3
Product_45 Need Attention €20,423 2
Product_45 Slipping €21,655 3
Product_45 Promising €59,317 3
Product_45 Total €122,159 12
Product_46 Need Attention €65,448 48
Product_46 Loyal €102,464 14
Product_46 At Risk €107,044 39
Product_46 Promising €165,365 16
Product_46 Slipping €214,720 26
Product_46 Total €655,042 143
Product_47 Slipping €15,351 12
Product_47 Champions €21,079 2
Product_47 Need Attention €29,218 9
Product_47 Loyal €64,342 9
Product_47 At Risk €95,905 12
Product_47 Promising €123,773 20
Product_47 Total €349,668 64
Product_48 Promising €1,203 2
Product_48 Slipping €30,100 4
Product_48 Need Attention €246,376 3
Product_48 Total €277,679 9
Product_49 Champions €11,969 1
Product_49 Promising €375,805 4
Product_49 Total €387,774 5
Product_5 Slipping €142,046 35
Product_5 Need Attention €330,535 54
Product_5 Promising €633,745 28
Product_5 Loyal €4,422,451 36
Product_5 At Risk €5,228,582 118
Product_5 Champions €22,201,009 62
Product_5 Total €32,958,368 333
Product_50 Loyal €12,386 2
Product_50 Slipping €47,951 10
Product_50 Promising €113,170 10
Product_50 Need Attention €205,885 3
Product_50 At Risk €234,251 11
Product_50 Total €613,643 36
Product_51 Promising €12,441 3
Product_51 Slipping €28,404 9
Product_51 Champions €44,814 3
Product_51 Loyal €150,201 7
Product_51 Need Attention €245,074 10
Product_51 At Risk €927,823 5
Product_51 Total €1,408,757 37
Product_52 Slipping €19,976 1
Product_52 Need Attention €41,863 1
Product_52 Promising €48,025 1
Product_52 At Risk €197,895 2
Product_52 Total €307,758 5
Product_53 At Risk €2,352 4
Product_53 Need Attention €2,641 4
Product_53 Loyal €6,600 3
Product_53 Promising €8,176 8
Product_53 Slipping €14,140 2
Product_53 Total €33,908 21
Product_54 Slipping €5,600 2
Product_54 Promising €6,800 2
Product_54 Total €12,400 4
Product_55 Slipping €5,800 1
Product_55 Loyal €25,110 1
Product_55 At Risk €97,500 1
Product_55 Champions €227,000 1
Product_55 Promising €413,100 3
Product_55 Total €768,510 7
Product_56 Promising €21,654 3
Product_56 At Risk €306,800 1
Product_56 Total €328,454 4
Product_57 Loyal €54,157 1
Product_57 Need Attention €54,157 1
Product_57 Promising €99,577 3
Product_57 Champions €108,314 2
Product_57 Total €316,205 7
Product_58 Loyal €3,170 1
Product_58 Promising €11,000 1
Product_58 Slipping €24,728 2
Product_58 At Risk €33,369 2
Product_58 Total €72,267 6
Product_59 At Risk €557,600 1
Product_59 Total €557,600 1
Product_6 Slipping €85,173 23
Product_6 Promising €532,466 24
Product_6 Need Attention €574,809 47
Product_6 Loyal €842,729 29
Product_6 Champions €1,388,716 20
Product_6 At Risk €1,908,955 67
Product_6 Total €5,332,848 210
Product_60 Need Attention €1,444 1
Product_60 Loyal €3,833 3
Product_60 Total €5,277 4
Product_61 Promising €1,100 1
Product_61 Total €1,100 1
Product_62 Promising €27,460 1
Product_62 Total €27,460 1
Product_7 Loyal €39,963 6
Product_7 Need Attention €62,016 7
Product_7 Slipping €80,332 8
Product_7 Promising €110,574 9
Product_7 Champions €1,700,079 1
Product_7 At Risk €1,747,961 6
Product_7 Total €3,740,925 37
Product_8 Slipping €426,570 56
Product_8 Loyal €1,765,114 27
Product_8 Need Attention €4,456,600 37
Product_8 Champions €5,211,674 7
Product_8 Promising €5,967,544 59
Product_8 At Risk €13,838,037 52
Product_8 Total €31,665,539 238
Product_9 Need Attention €1,789 4
Product_9 Promising €5,557 3
Product_9 Loyal €89,084 1
Product_9 At Risk €157,747 5
Product_9 Total €254,178 13
segtoview="At Risk"
la<-revprod%>%filter(Segment==segtoview)

tree2<-revprod%>%filter(Segment==segtoview)%>%ggplot(aes(area =Revenue, fill =Revenue, label = paste(Product.Name,format(as.numeric(Revenue/1000000),digits=0,nsmall=2, big.mark=","),"M€")))+geom_treemap() +geom_treemap_text(fontface = "italic", colour = "white", place = "centre",grow = T,reflow=T)+theme(legend.position = "right")+labs(title =paste("Revenue per Product" ,segtoview, "Segment\n",segtoview, "Revenue=",format(sum(la$Revenue),big.mark = ","),"€"),caption = "The area of each tile represents the proportion of Revenue")+scale_fill_continuous(low="orangered1",high="darkblue",label = dollar_format(suffix = "€", prefix = ""))
tree2

revseg2017%>%ggplot(aes(Segment,Revenue))+geom_bar(stat="identity",fill="steel blue")+geom_text(aes(label=Revenue),size=3.5)+scale_y_continuous(breaks=seq(0,90000000,15000000),labels = dollar_format(suffix = "€", prefix = ""))+theme_minimal()+coord_flip()

#+scale_x_discrete(limits=rev(myorder))+coord_flip()